using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CrudI.Interface;

namespace SQLCodeCreator.DBInfo.SqlServer
{    
    public class SqlDatabase
    {
        string _DatabaseName;
        static List<ITable> _tableList;

        public List<ITable> TableList
        {
            get { return _tableList; }
            set { _tableList = value; }
        }
        public string DatabaseName
        {
            get { return _DatabaseName; }
            set { _DatabaseName = value; }
        }


        static SqlDatabase()
        {
            _tableList = new List<ITable>();
        }

        public static List<SqlDatabase> GetServerDatabases(SqlConnectionStringBuilder sqlConnectionString)
        {
            string sqlSelectDatabases = " SELECT * FROM sys.databases " +
            " Order By Name";

            DataTable dt = new DataTable("dtBugs");
            SqlConnection conn = new SqlConnection(sqlConnectionString.ToString());
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlSelectDatabases;
            
            try
            {
                cmd.Connection.Open();
                IDataReader rd = cmd.ExecuteReader();
                dt.Load(rd);
            }
            catch (Exception ex)
            {
                throw new DataException("Error opening server for DB select", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            List<SqlDatabase> databaseList = ConvertDataTableToObject(dt);
            return databaseList;
        }

        private static List<SqlDatabase> ConvertDataTableToObject(DataTable dt)
        {
            SqlDatabase databaseInfo = null;
            List<SqlDatabase> databaseList = new List<SqlDatabase>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                
                if (dt.Columns.Contains("Name"))
                {
                    databaseInfo = new SqlDatabase();                    
                    databaseInfo.DatabaseName = dt.Rows[i]["Name"].ToString();
                    databaseList.Add(databaseInfo);
                }
            }
            return databaseList;
        }

        public static List<ITable> GetTables(string connectionString)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn1 = new SqlConnection(connectionString))
            {

                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME", conn1);
                cmd.Connection = conn1;
                cmd.CommandType = CommandType.Text;

                try
                {
                    cmd.Connection.Open();
                    IDataReader rd = cmd.ExecuteReader();
                    dt.Load(rd);
                }
                catch (Exception ex)
                {
                    throw new DataException("Error opening DB for bug select.", ex);
                }
                finally
                {
                    if (conn1.State == ConnectionState.Open)
                        conn1.Close();
                }
            }

            List<ITable> tableList = ConvertDataTableToTableObject(dt);
            return tableList;
        }

        private static List<ITable> ConvertDataTableToTableObject(DataTable dt)
        {
            ITable table = null;
            List<ITable> tables = new List<ITable>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                table = new SqlTable();
                if (dt.Columns.Contains("TABLE_NAME"))
                {
                    table.TableName = dt.Rows[i]["TABLE_NAME"].ToString();
                }
                tables.Add(table);
            }
            return tables;
        }
    }
}
